﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_msx_group_servermembers]
#-- Purpose:		Retrieve the servers that belong to MSX groups
#--	Last Update:	09/18/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_msx_group_servermembers]
(
	@servergroup_id			int			= NULL
)
AS

SET NOCOUNT ON

SELECT		tsg.servergroup_id,
			ts.server_id,
			ts.server_name,
			ts.enlist_date,
			ts.last_poll_date
FROM		[msdb].[dbo].[systargetservergroups] tsg
JOIN		[msdb].[dbo].[systargetservergroupmembers] tsgm ON tsg.servergroup_id = tsgm.servergroup_id
JOIN		[msdb].[dbo].[systargetservers] ts ON tsgm.server_id = ts.server_id
WHERE		tsg.servergroup_id = @servergroup_id
			OR @servergroup_id IS NULL

UNION

SELECT		NULL servergroup_id,
			ts.server_id,
			ts.server_name,
			ts.enlist_date,
			ts.last_poll_date
FROM		[msdb].[dbo].[systargetservers] ts 
LEFT JOIN	[msdb].[dbo].[systargetservergroupmembers] tsgm ON tsgm.server_id = ts.server_id
WHERE		tsgm.server_id IS NULL

ORDER BY	1, 3

SET NOCOUNT OFF